import pandas as pd
import plotly.express as px
from post_roe.query import TinyQuery as tq
def _total_loss(state_status):
if state_status == 'at_risk':
return 1
else:
return 0
adi = tq.adi_with_status_query()
adi['_loss'] = adi['state_status'].apply(_total_loss)
adi
| state | zip5 | count | min | max | adi_mean | adi_median | std | lat | lon | population | state_status | _status_wp | _adi_decile | _loss | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | AK | 99501 | 2988 | 7.0 | 97.0 | 33.152276 | 36.0 | 18.076871 | 61.219998 | -149.857840 | 17603 | protected | Legal and likely to be protected | 3 | 0 |
| 1 | AK | 99502 | 2745 | 11.0 | 43.0 | 24.122404 | 25.0 | 8.677508 | 61.163652 | -149.996643 | 24168 | protected | Legal and likely to be protected | 2 | 0 |
| 2 | AK | 99503 | 2524 | 10.0 | 72.0 | 39.593502 | 41.0 | 8.925617 | 62.043689 | -158.174466 | 14563 | protected | Legal and likely to be protected | 4 | 0 |
| 3 | AK | 99504 | 3420 | 19.0 | 98.0 | 36.341228 | 31.0 | 16.996213 | 61.190578 | -149.607154 | 40914 | protected | Legal and likely to be protected | 3 | 0 |
| 4 | AK | 99505 | 1020 | 31.0 | 31.0 | 31.000000 | 31.0 | 0.000000 | 61.284745 | -149.653973 | 6174 | protected | Legal and likely to be protected | 3 | 0 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 31525 | WY | 83124 | 267 | 38.0 | 74.0 | 48.921348 | 38.0 | 16.580783 | 41.758808 | -110.315880 | 137 | at_risk | Ban within 30 days | 3 | 1 |
| 31526 | WY | 83126 | 281 | 33.0 | 33.0 | 33.000000 | 33.0 | 0.000000 | 42.581108 | -110.904209 | 334 | at_risk | Ban within 30 days | 3 | 1 |
| 31527 | WY | 83127 | 1689 | 27.0 | 39.0 | 36.278863 | 39.0 | 4.083667 | 42.918972 | -110.997753 | 3041 | at_risk | Ban within 30 days | 3 | 1 |
| 31528 | WY | 83128 | 892 | 23.0 | 44.0 | 42.233184 | 44.0 | 5.224854 | 43.040079 | -110.722208 | 1601 | at_risk | Ban within 30 days | 4 | 1 |
| 31529 | WY | 83414 | 111 | 3.0 | 22.0 | 3.171171 | 3.0 | 1.803400 | 43.861439 | -110.935725 | 544 | at_risk | Ban within 30 days | 0 | 1 |
31400 rows × 15 columns
adi_decile = adi.groupby(['_loss','_adi_decile']).agg(population=("population","sum")).reset_index()
px.bar(
adi_decile,
x='_adi_decile',
y="population",
facet_col='_loss',
title="Population by ADI Decile Distribution | Protected vs At-Risk States",
height=400
).show(renderer='notebook')
adi_decile
| _loss | _adi_decile | population | |
|---|---|---|---|
| 0 | 0 | 0 | 20547321 |
| 1 | 0 | 1 | 25770448 |
| 2 | 0 | 2 | 22733956 |
| 3 | 0 | 3 | 16789534 |
| 4 | 0 | 4 | 13838009 |
| 5 | 0 | 5 | 9901878 |
| 6 | 0 | 6 | 7860189 |
| 7 | 0 | 7 | 6219300 |
| 8 | 0 | 8 | 4234561 |
| 9 | 0 | 9 | 2306551 |
| 10 | 1 | 0 | 1932551 |
| 11 | 1 | 1 | 6664790 |
| 12 | 1 | 2 | 10858248 |
| 13 | 1 | 3 | 17221953 |
| 14 | 1 | 4 | 21195259 |
| 15 | 1 | 5 | 25933649 |
| 16 | 1 | 6 | 27622211 |
| 17 | 1 | 7 | 27223894 |
| 18 | 1 | 8 | 23993984 |
| 19 | 1 | 9 | 15199419 |
px.density_heatmap(adi, y='state_status', x='_adi_decile', z='population').show(renderer="notebook")